package com.apes.scm.account.transit;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2020-02-12 18:41
 */
public interface DepositInTransitRepository extends CustomRepository<DepositInTransit, Long> {
    /**
     * fetch data by rule id
     *
     * @return List<Map>
     */
    @Query(value = "" +
            "SELECT 'Total'SOURCE_BUSINESS_TYPE,'总计' LEFTTEXT ,COUNT(*) RIGHTTEXT\n" +
            "            FROM ACC_DEPOSIT_IN_TRANSIT T WHERE T.CREATE_DATE < SYSDATE -3\n" +
            "UNION ALL\n" +
            "SELECT SOURCE_BUSINESS_TYPE,(SELECT A.NAME FROM SOE_BUSINESS_TYPE A WHERE A.ID = T.SOURCE_BUSINESS_TYPE)LEFTTEXT ,COUNT(*) RIGHTTEXT\n" +
            "            FROM ACC_DEPOSIT_IN_TRANSIT T WHERE T.CREATE_DATE < SYSDATE -3 GROUP BY T.SOURCE_BUSINESS_TYPE", nativeQuery = true)
    List<Map> groupFindAllDepositInTransit();

    @Query(value = "" +
            "SELECT T.ID,CUSTODY_QTY,TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')  CREATE_DATE, ORDER_NUMBER, ORDER_SERIAL_NUMBER, SOURCE_BUSINESS_TYPE, " +
            "(SELECT A.NAME FROM SOE_BUSINESS_TYPE A WHERE A.ID = T.SOURCE_BUSINESS_TYPE) LEFTTEXT,\n" +
            "             (CASE\n" +
            "               WHEN T.SOURCE_BUSINESS_TYPE = 'ProfitAndLoss' THEN\n" +
            "                (SELECT DECODE(B.TYPE, 'loss', 'form/stock.productLoss',\n" +
            "                                'form/stock.productProfit')\n" +
            "                   FROM STOCK_PROFIT_LOSS B\n" +
            "                  WHERE B.ID = T.ORDER_NUMBER)\n" +
            "               ELSE\n" +
            "                (SELECT S.FORM_ID\n" +
            "                   FROM SOE_BUSINESS_TYPE S\n" +
            "                  WHERE S.ID = T.SOURCE_BUSINESS_TYPE)\n" +
            "             END) FORM_ID," +
            "       USABLE_QTY,decode(T.NEXT_OPERATION, '收货', '收货（请使用扫码机收货）', '发货', '发货（请使用扫码机发货）', T.NEXT_OPERATION || '(请使用扫码机收货/发货)') NEXT_OPERATION, OCCUR_QTY,\n" +
            "       COMPANY_ID,(SELECT B.NAME  FROM MAS_PARTY B, MAS_PARTY_ROLE C WHERE C.PARTY_ID = B.ID AND C.ID = T.COMPANY_ID) COMPANY_NAME,\n" +
            "       LOCATION_ID,(SELECT A.NAME FROM MAS_LOCATION A WHERE A.ID=T.LOCATION_ID) LOCATION_NAME,\n" +
            "       PRODUCT_ID,(SELECT A.NAME FROM MAS_PRODUCT A WHERE A.ID=T.PRODUCT_ID) PRODUCT_NAME\n" +
            "FROM ACC_DEPOSIT_IN_TRANSIT T WHERE T.SOURCE_BUSINESS_TYPE=?1 AND T.CREATE_DATE < SYSDATE -3 ORDER BY T.ID ",nativeQuery = true)
    List<Map> findBySourceBusinessType(String sourceBusinessType);

    @Query(value = "" +
            "SELECT T.ID,CUSTODY_QTY,TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')  CREATE_DATE, ORDER_NUMBER, ORDER_SERIAL_NUMBER, SOURCE_BUSINESS_TYPE, " +
            "(SELECT A.NAME FROM SOE_BUSINESS_TYPE A WHERE A.ID = T.SOURCE_BUSINESS_TYPE) LEFTTEXT,\n" +
            "             (CASE\n" +
            "               WHEN T.SOURCE_BUSINESS_TYPE = 'ProfitAndLoss' THEN\n" +
            "                (SELECT DECODE(B.TYPE, 'loss', 'form/stock.productLoss',\n" +
            "                                'form/stock.productProfit')\n" +
            "                   FROM STOCK_PROFIT_LOSS B\n" +
            "                  WHERE B.ID = T.ORDER_NUMBER)\n" +
            "               ELSE\n" +
            "                (SELECT S.FORM_ID\n" +
            "                   FROM SOE_BUSINESS_TYPE S\n" +
            "                  WHERE S.ID = T.SOURCE_BUSINESS_TYPE)\n" +
            "             END) FORM_ID," +
            "       USABLE_QTY,decode(T.NEXT_OPERATION, '收货', '收货（请使用扫码机收货）', '发货', '发货（请使用扫码机发货）', T.NEXT_OPERATION || '(请使用扫码机收货/发货)') NEXT_OPERATION, OCCUR_QTY,\n" +
            "       COMPANY_ID,(SELECT B.NAME  FROM MAS_PARTY B, MAS_PARTY_ROLE C WHERE C.PARTY_ID = B.ID AND C.ID = T.COMPANY_ID) COMPANY_NAME,\n" +
            "       LOCATION_ID,(SELECT A.NAME FROM MAS_LOCATION A WHERE A.ID=T.LOCATION_ID) LOCATION_NAME,\n" +
            "       PRODUCT_ID,(SELECT A.NAME FROM MAS_PRODUCT A WHERE A.ID=T.PRODUCT_ID) PRODUCT_NAME\n" +
            "FROM ACC_DEPOSIT_IN_TRANSIT T WHERE T.CREATE_DATE < SYSDATE -3 ORDER BY T.ID ",nativeQuery = true)
    List<Map> findBySourceBusinessType();

}
